package com.yunfei.excel_insert.web.controller;

import com.yunfei.excel_insert.web.dao.ZhangyunfeiExcelInsertMapper;
import com.yunfei.excel_insert.web.dataobject.ReturnContent;
import com.yunfei.excel_insert.web.dataobject.ZhangyunfeiExcelInsert;


import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.Part;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: yunfei
 * @Date: 19:34
 */
@RestController
public class ExcelInsertController {
    @Autowired
    ZhangyunfeiExcelInsertMapper zhangyunfeiExcelInsertMapper;
    ReturnContent returnContent;


    //单条数据的插入
    @RequestMapping(path = "/insert")
    public String insert(@RequestParam("telNumber") String telNumber,
                         @RequestParam("port") String port,
                         ZhangyunfeiExcelInsert excelInsert, ModelMap modelMap) {
        modelMap.addAttribute("return-content", returnContent);

        //先判断手机号是否存在，再判断端口号是否存在，再进行插入数据
        if (zhangyunfeiExcelInsertMapper.selectByPrimaryKey(telNumber) != null) {
            zhangyunfeiExcelInsertMapper.updateByPrimaryKey(excelInsert);
            returnContent.setReturnText("电话号码已经存在，信息已经更新");
            return "insert-page";
        } else if (zhangyunfeiExcelInsertMapper.selectByPort(port) != null) {
            zhangyunfeiExcelInsertMapper.updateByPrimaryKey(excelInsert);
            returnContent.setReturnText("端口已经存在，更新信息");
            return "insert-page";
        } else {
            zhangyunfeiExcelInsertMapper.insert(excelInsert);
            returnContent.setReturnText("插入成功");
            return "insert-page";
        }
    }

    //      通过电话号码单体数据的查询
    @RequestMapping(path = "/query")
    //有待完善 。。查询没有手机号时，返回查询页面
    public String query(@RequestParam("telNumber") String telNumber) {
        ZhangyunfeiExcelInsert queryTemp = zhangyunfeiExcelInsertMapper.selectByPrimaryKey(telNumber);
        //List selectall转map <String,Zhangyunfei_ExcelInsert> telNumber作为key
        Map<String, ZhangyunfeiExcelInsert> map = new HashMap<>();
        for (ZhangyunfeiExcelInsert ei : zhangyunfeiExcelInsertMapper.selectAll()
        ) {
            map.put(ei.getTelNumber(), ei);

        }

        if (!map.containsKey(telNumber)) {
            return "输入的手机号错误了";
        } else {
            return "手机号：" + queryTemp.getTelNumber() + "员工姓名：" + queryTemp.getStaffName() + "端口号：" +
                    queryTemp.getPort() + "端口密码：" + queryTemp.getPassword();
        }

    }

    //      查询数据库里面的所有端口密码
    @RequestMapping(path = "/queryall")
    public String queryAll() {
        List<ZhangyunfeiExcelInsert> records = zhangyunfeiExcelInsertMapper.selectAll();

        return "端口：" + "\t" + "端口密码：" + "\t" +
                "电话号码：" + "\t" + "员工姓名：" + "\t" + "</Br>" +
                records.toString();
    }


    //    单个execl进行多条数据的插入
    @RequestMapping(path = "/importexcel")
    public String importExcel(@RequestParam("excel-file") File excelFile) throws IOException {

        if (!excelFile.exists()) {
//
            return "路径无效,选择有效路径";
        } else {
            Workbook workBook = null;
            //                workBook = new XSSFWorkbook(file);
            try {
                workBook = new XSSFWorkbook(excelFile);
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            }
            Sheet sheet = workBook.getSheetAt(0);
            int rowNum = sheet.getLastRowNum();
//            调用插入方法
            insertMethod(sheet, rowNum);


            return "excel数据插入成功";
        }

    }

    @RequestMapping(path = "/delete")
    public String deleteByTelNumber(@RequestParam("tel-number") String telNumber) {
//        先判断是否存在
        ZhangyunfeiExcelInsert queryTemp = zhangyunfeiExcelInsertMapper.selectByPrimaryKey(telNumber);
        //List selectall转map <String,Zhangyunfei_ExcelInsert> telNumber作为key
        Map<String, ZhangyunfeiExcelInsert> map = new HashMap<>();
        for (ZhangyunfeiExcelInsert ei : zhangyunfeiExcelInsertMapper.selectAll()
        ) {
            map.put(ei.getTelNumber(), ei);

        }

        if (!map.containsKey(telNumber)) {
            return "输入的手机号不存在";
        } else {

            return "删除成功";
        }
    }

    @RequestMapping(
            path = {"/update"}
    )
    public String update(@RequestParam("telNumber") String telNumber,
                         @RequestParam("port") String port, ZhangyunfeiExcelInsert excelInsert,
                         ModelMap modelMap) {
        if (this.zhangyunfeiExcelInsertMapper.selectByPrimaryKey(telNumber) != null) {
            this.zhangyunfeiExcelInsertMapper.updateByPrimaryKey(excelInsert);
            returnContent.setReturnText("电话号码已经存在，信息已经更新");
            modelMap.addAttribute("return-content", returnContent);
            return "insert-page";
        } else if (this.zhangyunfeiExcelInsertMapper.selectByPort(port) != null) {
            this.zhangyunfeiExcelInsertMapper.updateByPrimaryKey(excelInsert);
            return "端口已经存在，更新信息";
        } else {
            this.zhangyunfeiExcelInsertMapper.insert(excelInsert);
            return "插入成功";
        }
    }

    //    多个excel进行多条数据的插入 也可单个excel插入
    @RequestMapping(path = "/import-allexcel")
    public String importAllExcel(@RequestParam("all-file") MultipartFile[] multipartFiles) {
        if (multipartFiles.length > 0) {
            for (MultipartFile item : multipartFiles) {
                if (item.getSize() > 0) {
                    Workbook workBook = null;

                    try {
                        workBook = new XSSFWorkbook((File) item);
                    } catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException e) {
                        e.printStackTrace();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                    Sheet sheet = workBook.getSheetAt(0);
                    int rowNum = sheet.getLastRowNum();
                    insertMethod(sheet, rowNum);
                }
            }
            return "多个excel插入成功";
        } else {
            return "插入失败";
        }


    }

    public void insertMethod(Sheet sheet, int rowNum) {

        for (int i = 1; i < rowNum; i++) {
            ZhangyunfeiExcelInsert ei = new ZhangyunfeiExcelInsert();
            Row row = sheet.getRow(i);
            String port = row.getCell(0).getStringCellValue();
            String password = row.getCell(1).getStringCellValue();
            String staffName = row.getCell(2).getStringCellValue();
            String telNumber = row.getCell(3).getStringCellValue();
            if (zhangyunfeiExcelInsertMapper.selectByPrimaryKey(telNumber) != null) {
                continue;
            }
            if (zhangyunfeiExcelInsertMapper.selectByPort(port) != null) {
                continue;
            }
            ei.setPort(port);
            ei.setPassword(password);
            ei.setStaffName(staffName);
            ei.setTelNumber(telNumber);
            zhangyunfeiExcelInsertMapper.insert(ei);
        }


    }
}
